import pandas as pd
import openpyxl

# 假设你的Excel文件路径是 'your_file.xlsx'
file_path_src = 'timeTable.xlsx'
file_path_target = "江财Normal工作室_每日统计_20250317-20250323.xlsx"
# 使用read_excel读取Excel文件，跳过前两行，第三行作为表头
df_src = pd.read_excel(file_path_src, header=0)
df_target = pd.read_excel(file_path_target, header=2)
# 打印DataFrame查看结果
#print(df_src)
#print(df_target)
StaticResult=''
for i in range(df_target.shape[0]):
    name=df_target.loc[i,'姓名']
    date=str(df_target.loc[i,'日期'])[-3:]

    for j in range(df_src.shape[0]):
        name_t=df_src.loc[j,'姓名']
        date_t=str(df_src.loc[j,'日期'])[-3:]
        if name==name_t and date==date_t:
            with open('StatisticResult.txt', 'w',encoding='cp1252') as file:
                if df_target.loc[i,'上班1打卡结果']=='缺卡' and df_src.loc[j,'上午有课']!='是' and df_target.loc[i,'下班1打卡结果']=='缺卡' and df_src.loc[j,'上午有课']!='是':
                    StaticResult=StaticResult+(name+','+date+','+'上午缺卡\n')            
                if df_target.loc[i,'上班2打卡结果']=='缺卡' and df_src.loc[j,'下午有课']!='是' and df_target.loc[i,'下班2打卡结果']=='缺卡' and df_src.loc[j,'下午有课']!='是':
                    StaticResult=StaticResult+(name+','+date+','+'下午缺卡\n')          
                if df_target.loc[i,'上班3打卡结果']=='缺卡' and df_src.loc[j,'晚上有课']!='是' and df_target.loc[i,'下班3打卡结果']=='缺卡' and df_src.loc[j,'晚上有课']!='是':
                    StaticResult=StaticResult+(name+','+date+','+'晚上缺卡\n')          
with open('StatisticResult.txt', 'w', encoding='utf-8') as file:
    file.write(StaticResult)    

'''
            with open('StaticResult.txt', 'w', encoding='utf-8') as file:
                if df_target.loc[i,'上班1打卡结果']=='缺卡' and df_src.loc[j,'上午有课']!='是':
                    StaticResult=StaticResult+(name+date+'上午缺卡\n')            
                if df_target.loc[i,'下班1打卡结果']=='缺卡' and df_src.loc[j,'上午有课']!='是':
                    StaticResult=StaticResult+(name+date+'上午缺卡\n')            
                if df_target.loc[i,'上班2打卡结果']=='缺卡' and df_src.loc[j,'下午有课']!='是':
                    StaticResult=StaticResult+(name+date+'下午缺卡\n')            
                if df_target.loc[i,'下班2打卡结果']=='缺卡' and df_src.loc[j,'下午有课']!='是':
                    StaticResult=StaticResult+(name+date+'下午缺卡\n')          
                if df_target.loc[i,'上班3打卡结果']=='缺卡' and df_src.loc[j,'晚上有课']!='是':
                    StaticResult=StaticResult+(name+date+'晚上缺卡\n')            
                if df_target.loc[i,'下班3打卡结果']=='缺卡' and df_src.loc[j,'晚上有课']!='是':
                    StaticResult=StaticResult+(name+date+'晚上缺卡\n')     
    '''